#Calendar table
Absolute Last Invoice Date = 
 CALCULATE(
           LASTDATE(Invoice[Date])
          ,ALL(Invoice)
          )
CurrentFyMonth = 
    IF(
       [FyYear]=YEAR([Absolute Last Invoice Fiscal Date]) 
       && 
       [FyMonth]=MONTH([Absolute Last Invoice Fiscal Date]) 
       ,1
       ,0
       )
CurrentFyYear = 
    IF(
       [FyYear]=YEAR([Absolute Last Invoice Fiscal Date])
       ,1
       ,0
    )
Last fiscal month of the year = 
    CALCULATE(
        LASTNONBLANK('Calendar'[Date]
                     ,[Sum of Revenue])
        ,ALLEXCEPT('Calendar'
                   ,'Calendar'[FyYear])
        )
LastFyMonthofYear = 
    IF(
        [Year]=YEAR([Last fiscal month of the year])
        &&
        [Month]=MONTH([Last fiscal month of the year])
       ,1
       ,0
      )		
Running 12 FY months = 
  IF(
      [Date] >= EOMONTH(
    [Absolute Last Invoice Date]
    ,-12
           		    )+1 
    	  && 
      [Date] <= EOMONTH(
 				    [Absolute Last Invoice Date]
  				    ,0
          		  ) 
   	   ,1
    	   ,0)
#invoice
Absolute Last Invoice Fiscal Date = CALCULATE(
        LASTDATE(Invoice[Fiscal Date])
        , ALL(Invoice)
        ) 
Avg GrowthRate last 6 months = 
 CALCULATE(
           AVERAGEX(
                    ALL('Calendar'[YearMonth])
                    ,[Sum of Revenue YoY%])
            ,ALL('Calendar')
            ,DATESINPERIOD('Calendar'[Date]
                   ,CALCULATE(
                             LASTNONBLANK('Calendar'[Date]
                                         ,[Sum of Revenue])
                             ,ALL('Calendar'))
                    ,[getTargetMonths] * -1
                    ,MONTH)
            )
Fiscal Date = DATE(
    RELATED(Calendar[FyYear])
    ,RELATED(Calendar[FyMonth])
    ,1
    )
isReportSlicerSet = 
 HASONEVALUE(varPeriod[Period])	
Last Revenue = 
    CALCULATE([Sum of Revenue]
        , 'Calendar'[CurrentFyMonth]=1
        ,ALL('Calendar')
        )
MonthsSinceCurrentMonth = 
    (
      YEAR(STARTOFMONTH('Calendar'[Date]))
      -
        YEAR([Absolute Last Invoice Date])
      ) * 12
      +
      MONTH(STARTOFMONTH('Calendar'[Date]))
      -
      MONTH([Absolute Last Invoice Date])
Nr of markets = 
 DISTINCTCOUNT(Invoice[PoliticalGeographyKey])
Pct of all products = 
 DIVIDE([Sum of Revenue], [Revenue all products])
ProjectedGrowthFactor = 
    POWER(1 + [Avg GrowthRate last 6 months]
         ,[MonthsSinceCurrentMonth])
ProjectedRevenue = 
    IF([MonthsSinceCurrentMonth] >= 0
    ,[Last Revenue] * [ProjectedGrowthFactor])
Revenue all products = 
 CALCULATE([Sum of Revenue],ALL('Product'))
Revenue by Units = 
 DIVIDE([Sum of Revenue],[Sum of Units])
Revenue by Units Previous Year = 
 IF(HASONEVALUE('Calendar'[FyYear]),
  CALCULATE([Revenue by Units],   
            SAMEPERIODLASTYEAR('Calendar'[Date])
            )
    )
Revenue by Units YoY% = 
IF([Revenue by Units]>0,
        DIVIDE([Revenue by Units] 
            -[Revenue by Units Previous Year]
        ,[Revenue by Units Previous Year])
   )
Revenue to target = 
 [Sum of Revenue] - [Sum of Revenuetarget]
 Revenue to target F QTD = [Sum of Revenue F QTD]-[Sum of RevenueTarget F QTD]
 Revenue to target F YTD = [Sum of Revenue F YTD]-[Sum of Revenuetarget F YTD]
 RevenueByPeriod = 
 IF([isReportSlicerSet], 
    SWITCH(VALUES(varPeriod[Period]), 
         "Actual",[Sum of Revenue], 
         "YTD",[Sum of Revenue F YTD], 
         "QTD",[Sum of Revenue F QTD]
         )
     )
RevenueTargetByPeriod = 
IF([isReportSlicerSet],
    SWITCH(VALUES(varPeriod[Period]) ,
        "Actual",[Sum of Revenuetarget],
        "YTD",[Sum of RevenueTarget F QTD],
        "QTD",[Sum of RevenueTarget F QTD]
        )
    )	 
RevenueToTargetByPeriod = 
IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]) , 
	    "Actual",[Revenue to target], 
		"YTD",[Revenue to target F YTD], 
		"QTD",[Revenue to target F QTD] 
		)
	)	
RevenueYoYByPeriod = 
IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Sum of Revenue YoY%], 
		"YTD",[Sum of Revenue F YTD YoY%], 
		"QTD",[Sum of Revenue F QTD YoY%] 
		)
	)
SubscriberCountByPeriod = 
IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Sum of SubscriberCount], 
		"YTD",[Sum of SubscriberCount F YTD], 
		"QTD",[Sum of SubscriberCount F QTD] 
		)
	)
SubscriberCountYoYByPeriod = 
IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Sum of SubscriberCount YoY%], 
		"YTD",[Sum of SubscriberCount F YTD YoY%],
		"QTD",[Sum of SubscriberCount F QTD YoY%]
		)
	)	
Sum of Revenue = 
        SUM(Invoice[RevenueAmount])	
Sum of Revenue F QTD = 
 IF(HASONEVALUE('Calendar'[FYQuarter]),
    TOTALQTD([Sum of Revenue]
             ,'Calendar'[Date])
    )
Sum of Revenue F QTD PreviousYear = 
if(HASONEVALUE('Calendar'[FYQuarter]), 
		CALCULATE([Sum of Revenue F QTD],
			DATEADD('Calendar'[Date],-1,YEAR))
	,blank())		
Sum of Revenue F QTD YoY% = 
if( NOT(ISBLANK([Sum of Revenue])),
		DIVIDE(([Sum of Revenue F QTD]
				-[Sum of Revenue F QTD PreviousYear])
	,[Sum of Revenue F QTD PreviousYear]))
Sum of Revenue F YTD = 
 IF(HASONEVALUE(Calendar[FyYear]),
    TOTALYTD([Sum of Revenue]
             ,'Calendar'[Date]
             ,"06/30")
    )
Sum of Revenue F YTD PreviousYear = 
 IF(HASONEVALUE('Calendar'[FyYear]), 
    CALCULATE([Sum of Revenue F YTD]
              ,DATEADD('Calendar'[Date]
                       , -1
                       , YEAR)
              )
    )
Sum of Revenue F YTD YoY% = 
 IF(NOT(ISBLANK([Sum of Revenue])), 
    DIVIDE([Sum of Revenue F YTD]
               - [Sum of Revenue F YTD PreviousYear]
           ,[Sum of Revenue F YTD PreviousYear])
    )	
Sum of Revenue PreviousYear = 
    IF(SELECTEDVALUE('Calendar'[FyYear]),
        CALCULATE([Sum of Revenue]
                                  ,DATEADD('Calendar'[Date]  
                                  ,-1
                                  ,YEAR
                                  )
                 )
        )
Sum of Revenue Two Year = 
    IF(
           HASONEVALUE('Calendar'[FyYear])
               ,CALCULATE([Sum of Revenue]
                     ,DATEADD('Calendar'[Date]
                              ,-2
                              ,YEAR
                              )
                    )
        )
Sum of Revenue YoY% = 
var revenue = [Sum of Revenue]
var revenueprevyear = [Sum of Revenue PreviousYear]
    return 
    IF(
      NOT(ISBLANK(revenue)), 
          DIVIDE( 
                  (revenue                                                              
                   - revenueprevyear)
                  ,revenueprevyear
                )
     )	
Sum of RevenueAmount CALC = 
		CALCULATE(
             SUM([RevenueAmount]), 
             ALL(PoliticalGeography[Region]))
Sum of RevenueTarget = 
                        SUM([Revenuetarget])
Sum of RevenueTarget F QTD = 
if(HASONEVALUE('Calendar'[FYQuarter]) && NOT(ISBLANK([Sum of Revenuetarget]))
				,TOTALQTD([Sum of Revenuetarget],'Calendar'[Date])
				,BLANK())
Sum of Revenuetarget F YTD = 
 IF(HASONEVALUE('Calendar'[FyYear]),
    TOTALYTD([Sum of Revenuetarget],
              'Calendar'[Date]
              ,"06/30")
    )
Sum of SubscriberCount = SUM([SubscriberCount])
Sum of SubscriberCount F QTD = 
if(HASONEVALUE('Calendar'[FYQuarter])
	,TOTALQTD([Sum of SubscriberCount],'Calendar'[Date])
	,BLANK())	 
Sum of SubscriberCount F QTD PreviousYear = if(HASONEVALUE('Calendar'[FYQuarter]), 
CALCULATE([Sum of SubscriberCount F QTD],DATEADD('Calendar'[Date],-1,YEAR))
,blank())
Sum of SubscriberCount F QTD YoY% = if( NOT(ISBLANK([Sum of SubscriberCount])), 
([Sum of SubscriberCount F QTD]-[Sum of SubscriberCount F QTD PreviousYear])/[Sum of SubscriberCount F QTD PreviousYear])
Sum of SubscriberCount F YTD = if(HASONEVALUE('Calendar'[FyYear]),
TOTALYTD([Sum of SubscriberCount],'Calendar'[Date], "06/30")
,BLANK())
Sum of SubscriberCount F YTD PreviousYear = if(HASONEVALUE('Calendar'[FyYear]), 
CALCULATE([Sum of SubscriberCount F YTD],DATEADD('Calendar'[Date], -1, year))
,blank())
Sum of SubscriberCount F YTD YoY% = 
if( NOT(ISBLANK([Sum of SubscriberCount])),
	([Sum of SubscriberCount F YTD]-[Sum of SubscriberCount F YTD PreviousYear])
	/
	[Sum of SubscriberCount F YTD PreviousYear]
	)
Sum of SubscriberCount previous year = 
if(HASONEVALUE('Calendar'[FyYear]),
		CALCULATE([Sum of SubscriberCount], SAMEPERIODLASTYEAR('Calendar'[Date]))
		,BLANK())	
Sum of SubscriberCount YoY% = 
	if( NOT(ISBLANK([Sum of SubscriberCount])), 
		([Sum of SubscriberCount]-[Sum of SubscriberCount Previous Year])
		/
		[Sum of SubscriberCount Previous Year])
Sum of Units = SUM([Units])
Sum of Units F QTD = if(HASONEVALUE('Calendar'[FYQuarter])
,TOTALQTD([Sum of Units],'Calendar'[Date])
,BLANK())
Sum of Units F QTD PreviousYear = if(HASONEVALUE('Calendar'[FYQuarter]), 
CALCULATE([Sum of Units F QTD],DATEADD('Calendar'[Date],-1,YEAR))
,blank())
Sum of Units F QTD YoY% = 
if( NOT(ISBLANK([Sum of Units])),
	([Sum of Units F QTD]-[Sum of Units F QTD PreviousYear])
	/
	[Sum of Units F QTD PreviousYear]
	)
Sum of Units F YTD = if(HASONEVALUE('Calendar'[FyYear]),
TOTALYTD([Sum of Units],'Calendar'[Date], "06/30")
,BLANK())
Sum of Units F YTD PreviousYear = if(HASONEVALUE('Calendar'[FyYear]), 
CALCULATE([Sum of Units F YTD],DATEADD('Calendar'[Date], -1, year))
,blank())
Sum of Units F YTD YoY% = 
if( NOT(ISBLANK([Sum of Units])),
	([Sum of Units F YTD]-[Sum of Units F YTD PreviousYear])/[Sum of Units F YTD PreviousYear])
Sum of Units PreviousYear = 
	CALCULATE([Sum of Units],
		DATEADD('Calendar'[Date],-1,YEAR))
Sum of Units YoY% = 
	if( NOT(ISBLANK([Sum of Units])), 
		([Sum of Units]-[Sum of Units PreviousYear])/[Sum of Units PreviousYear])
Sum of UnitsTarget = SUM([UnitsTarget])
Sum of UnitsTarget F QTD = if(HASONEVALUE('Calendar'[FYQuarter])
		,TOTALQTD([Sum of UnitsTarget],'Calendar'[Date])
		,BLANK())
Sum of Unitstarget F YTD = if(HASONEVALUE('Calendar'[FyYear]),
TOTALYTD([Sum of UnitsTarget],'Calendar'[Date], "06/30")
,BLANK())
Sum of Usage = SUM([Usage])
Sum of Usage F QTD = if(HASONEVALUE('Calendar'[FYQuarter])
			,TOTALQTD([Sum of Usage],'Calendar'[Date])
			,BLANK())		
Sum of Usage F QTD PreviousYear = if(HASONEVALUE('Calendar'[FYQuarter]), 
			CALCULATE([Sum of Usage F QTD],DATEADD('Calendar'[Date],-1,YEAR))
			,blank())
Sum of Usage F QTD YoY% = if( NOT(ISBLANK([Sum of Usage])), 
						([Sum of Usage F QTD]-[Sum of Usage F QTD PreviousYear])/[Sum of Usage F QTD PreviousYear])
Sum of Usage F YTD = if(HASONEVALUE('Calendar'[FyYear]),
						TOTALYTD([Sum of Usage],'Calendar'[Date], "06/30")
						,BLANK())						
Sum of Usage F YTD PreviousYear = if(HASONEVALUE('Calendar'[FyYear]), 
CALCULATE([Sum of Usage F YTD],DATEADD('Calendar'[Date], -1, year))
,blank())
Sum of Usage F YTD YoY% = if( NOT(ISBLANK([Sum of Usage])),([Sum of Usage F YTD]-[Sum of Usage F YTD PreviousYear])/[Sum of Usage F YTD PreviousYear])
Sum of Usage PreviousYear = CALCULATE([Sum of Usage],DATEADD('Calendar'[Date],-1,YEAR))
Sum of Usage YoY% = if( NOT(ISBLANK([Sum of Usage])), 
                         ([Sum of Usage]-[Sum of Usage PreviousYear])
                           / 
                          [Sum of Usage PreviousYear]
      )
Sum of Usagetarget = SUM([Usagetarget])
Sum of UsageTarget F QTD = if(HASONEVALUE('Calendar'[FYQuarter])
,TOTALQTD([Sum of UsageTarget],'Calendar'[Date])
,BLANK())
Sum of Usagetarget F YTD = if(HASONEVALUE('Calendar'[FyYear]),
TOTALYTD([Sum of Usagetarget],'Calendar'[Date], "06/30")
,BLANK())
Test Value = 
        IF(HASONEVALUE(PoliticalGeography[Region]) &&  
             HASONEVALUE('Calendar'[FyYearLabel]),
                VALUES(PoliticalGeography[Region]) & 
                " " & 
                VALUES('Calendar'[FyYearLabel])
)
Units to target = [Sum of Units] - [Sum of UnitsTarget]
Units to target F QTD = [Sum of Units F QTD]-[Sum of UnitsTarget F QTD]
Units to target F YTD = [Sum of Units F YTD] - [Sum of Unitstarget F YTD]
UnitsByPeriod = 
	IF([isReportSlicerSet], SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Sum of Units], 
		"YTD",[Sum of Units F YTD], 
		"QTD",[Sum of Units F QTD] )
	)
UnitsToTargetByPeriod = IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Units to target], 
		"YTD",[Units to target F YTD], 
		"QTD",[Units to target F QTD] )
		)
UnitsYoYByPeriod = IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]) , 
		"Actual",[Sum of Units YoY%], 
		"YTD",[Sum of Units F YTD YoY%], 
		"QTD",[Sum of Units F QTD YoY%]))
Usage to target = [Sum of Usage] - [Sum of Usagetarget]
Usage to target F QTD = [Sum of Usage F QTD]-[Sum of UsageTarget F QTD]
Usage to target F YTD = [Sum of Usage F YTD] - [Sum of Usagetarget F YTD]
UsageByPeriod = IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Sum of Usage], 
		"YTD",[Sum of Usage F YTD], 
		"QTD",[Sum of Usage F QTD] )
		)
UsageToTargetByPeriod = IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]), 
		"Actual",[Usage to target], 
		"YTD",[Usage to target F YTD], 
		"QTD",[Usage to target F QTD] )
	)
UsageYoYByPeriod = IF([isReportSlicerSet], 
	SWITCH(VALUES(varPeriod[Period]) , 
		"Actual",[Sum of USage YoY%], 
		"YTD",[Sum of Usage F YTD YoY%], 
		"QTD",[Sum of Usage F QTD YoY%])
		)	
#keymetrics
KeyMetricActual = SWITCH(SELECTEDVALUE(Keymetrics[Values])
						,"Revenue", FORMAT([RevenueByPeriod], "$#,##0")
						,"Subscribers", FORMAT([SubscriberCountByPeriod], "#,##")
						,"Units", FORMAT([UnitsByPeriod], "#,##")
						,"Usage",FORMAT([UsageByPeriod], "#,##")
                        )
KeyMetricSparkline = 
SWITCH(SELECTEDVALUE(Keymetrics[Values])
		,"Revenue", [Sum of Revenue]
		,"Subscribers", [Sum of SubscriberCount]
		,"Units", [Sum of Units]
		,"Usage", [Sum of Usage])
KeyMetricVTT = 
     SWITCH(SELECTEDVALUE(Keymetrics[Values])
	       ,"Revenue"
              ,FORMAT([RevenueToTargetByPeriod], "$#,##0")
		  ,"Subscribers"
              ,"n/a"
		  ,"Units"
              ,FORMAT([UnitsToTargetByPeriod], "#,##")
		  ,"Usage"
              ,FORMAT([UsageToTargetByPeriod], "#,##")
            )
KeyMetricYoY = 
     SWITCH(SELECTEDVALUE(Keymetrics[Values])
		  ,"Revenue", [RevenueYoYByPeriod]
		  ,"Subscribers", [SubscriberCountYoYByPeriod]
		  ,"Units", [UnitsYoYByPeriod]
		  ,"Usage",[UsageYoYByPeriod])

#varPeriod
Reporting Period = 
 IF(HASONEVALUE(varPeriod[Period]),			
SWITCH(VALUES(varPeriod[Period])					
 ,"Actual",      
         CALCULATE(     
                   LASTNONBLANK('Calendar'[FYMonthLabel],1)
                   ,'Calendar'[CurrentFyMonth]=1) 
         & " " &    
         CALCULATE( 
                   LASTNONBLANK('Calendar'[FyYearLabel],1)    
                   ,'Calendar'[CurrentFyMonth]=1)											
	,"QTD", 
          CALCULATE(                              
                 LASTNONBLANK('Calendar'[FyQuarterLabel],1)  
                  ,'Calendar'[CurrentFyMonth]=1) 

          & " " & 
         CALCULATE(  
                 LASTNONBLANK('Calendar'[FyYearLabel],1)  
                 ,'Calendar'[CurrentFyMonth]=1)
	,"YTD", 
          CALCULATE( 
                 LASTNONBLANK('Calendar'[FyYearLabel],1) 
                 ,'Calendar'[CurrentFyMonth]=1)
	)
	, "N/A")
#varTargetMonths
getTargetMonths = 
    SELECTEDVALUE(varTargetMonths[TargetMonths]
                  ,12)